package com.wxtf.platform.main.jcqkgl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import com.wxtf.platform.tools.FormatUtil;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;


/**
 * <li>Title: sygl_tognjiAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.sygl</li>
 * <li>Description: 生育情况统计表</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author wangli
 * @version 1.0.0.0
 * 
 */
public class jcqkgl_tognjiAction extends LogicBase implements
		CommonsCallProvider {
	private static final Log log = LogFactory
			.getLog(jcqkgl_tognjiAction.class);

	public jcqkgl_tognjiAction(String jsonArray) {

	}

	@Override
	public Object run() {
		Object msg = "";
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		try {
			if ("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if ("download".equals(this.getMethodName())) {
				msg = this.download(this.getLogicData());
			}
			else{
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(user.getUserrelName(), e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		StringBuffer sb = new StringBuffer();
		StringBuffer sbs = new StringBuffer();
		StringBuffer sb_1 = new StringBuffer();
		
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			String[] subOrg;
			String strOrg="";
			//System.out.println("types---"+types);
			if("init".equals(types)) {// 初始查询
				subOrg = user.getCurSubOrgId().split(",");
				strOrg = user.getCurSubOrgId();//获取当前机构及以下的总数
			} else if("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串
				if(StringUtils.isNotBlank(orgIds)){
					subOrg = user.getCurSubOrgIds(orgIds).split(",");
				}else{
					subOrg = user.getCurSubOrgId().split(",");}
				strOrg = user.getCurSubOrgIds(orgIds);//获取当前某一机构及以下的总数
			}else{ subOrg = new String[0];  }
			if(subOrg.length>0)
				{
					int len=0;
					for(int m=0;m<subOrg.length;m++)
					{
						//查询当前组织机构的名称
						//System.out.println("组织机构："+subOrg[m]);
						String empSql = "SELECT CAPTION FROM t_department WHERE DEPARTMENT_ID ="+subOrg[m]+"";
						List<Object> lemp = AccessDataBaseUtil.query4List(empSql,null);
						if(lemp.size()>0)
						{
							//查出独生子女费[0-18岁]
							String shengyu="SELECT trpocfm_id as id,reserve2 as org_id,trpocfm_child_birthday as types from trp_only_child_fee_management where 1=1 ";

							//超生缴纳社会抚养费[已经登记的家庭]
							String nobirth ="SELECT trpecp_id as id,trpecp_penalty as org_id,'exceeds' as types from trp_exceeds_children_penalty where 1=1";
							
							//退休独生子女父母奖励费[要求获取独生子女证的夫妻]
							String credent="SELECT r.trpocrr_id as id,e.tbe_id as org_id,'record' as types from tbe_only_child_record o LEFT JOIN tb_family_base f on o.tbfi_id=f.tbfi_id LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id LEFT JOIN trp_only_child_retire_reward r on e.tbe_id=r.tbe_id where o.reserve2<>'1' and e.tbe_employment_forms <> '31' and e.tbe_status not in('102-4','102-3') ";
							
							len++;
							String name = lemp.get(0).toString();
							//System.out.println("名称："+name);
							if(m>0)
							{
								shengyu += " and org_id in (" + subOrg[m] + ") ";
								nobirth += " and org_id in (" + subOrg[m] + ") ";
								credent += " and e.org_id in (" + subOrg[m] + ") ";
							}
							else
							{
								shengyu += " and org_id in (" + strOrg + ") ";
								nobirth += " and org_id in (" + strOrg + ") ";
								credent += " and e.org_id in (" + strOrg + ") ";
							}
							String time1 = jsonObject.getString("time1");// 开始时间
							String time2 = jsonObject.getString("time2");// 结束时间
							StringBuffer buff = new StringBuffer();
							if(StringUtils.isNotBlank(time1)) {
								//领证时间
								shengyu += "and trpocfm_ocr_time >= '"+time1+"'";
								//缴款日期
								nobirth += "and trpecp_payment_time >= '"+time1+"'";
							}
							if(StringUtils.isNotBlank(time2)) {
								//领证时间
								shengyu += "and trpocfm_ocr_time <= '"+time2+" 23:59:59'";
								//缴款日期
								nobirth += "and trpecp_payment_time <= '"+time2+" 23:59:59'";
							}
							
							if(StringUtils.isBlank(time1)&&StringUtils.isBlank(time2))
							{
								buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 60 YEAR)");
								buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 50 YEAR)");
								buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 55 YEAR))");
								credent += buff.toString();
							}
							if(StringUtils.isNotBlank(time1)&&StringUtils.isNotBlank(time2))
							{
								buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
								buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
								buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
								credent += buff.toString();
							}
							else{
								if(StringUtils.isNotBlank(time1)) {
									//退休时间
									buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)");//男职工
									buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)");//女职工
									buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR))");//女干部
									credent += buff.toString();
									credent += buff.toString();
								}
								if(StringUtils.isNotBlank(time2)) {
									//退休时间
									buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
									buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
									buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
									credent += buff.toString();
									credent += buff.toString();
								}
							}
//							System.out.println("querySql:"+shengyu);
//							System.out.println("querySql:"+nobirth);
//							System.out.println("querySql:"+credent);
							String querySql = shengyu +" UNION "+nobirth+" UNION "+credent;
							//String[] fields = {"id", "org_id","types"};
//							System.out.println("querySql:"+querySql);
							List<Object[]> list = AccessDataBaseUtil.query(querySql,null);
							
							int onlytotal=0;//总人数
							int onlytotalMon=0;//独生子女费总金额
							int exceedstotal=0;//超生总人数
							float exceedstotalMon=0;//超生总金额
							int retiretotals=0;//已发放退休总人数
							float retiretotalMons=0;//已发放退休总金额
							int retiretotal=0;//退休总人数
							float retiretotalMon=0;//退休总金额
							if (list.size() > 0) {
								for (int i = 0; i < list.size(); i++) {// 行
									int sum=0;
									Object[] ids=list.get(i);
									if(ids[2].toString().equals("exceeds"))//超生
									{
										exceedstotal++;
										if(!ids[1].toString().equals("") && ids[1].toString() != null) {
											exceedstotalMon += Float.parseFloat(ids[1].toString());
											}
									}
									else if(ids[2].toString().equals("record"))//退休
									{
										//已发放
										if (!ids[0].toString().equals("")) {
											retiretotals += 1;
											retiretotalMons +=user.getGlobalMap().get("Retirement").equals("") ? 2000 : Float.parseFloat(user.getGlobalMap().get("Retirement"));
										}
										retiretotal++;
										//男职工2000元，女职工2000元，女干部2000元
										//从参数中取得:user.getGlobalMap().get("reward")
										retiretotalMon +=user.getGlobalMap().get("Retirement").equals("") ? 2000 : Float.parseFloat(user.getGlobalMap().get("Retirement"));
									}
									else
									{
										onlytotal++;
										if(ids[1].toString().equals(""))//查看证件是否注销
										{
											String sqloptions="Select trpocfm_fee_option,create_time from trp_only_child_fee_options where trpocfm_id = '"+ids[0].toString()+"' order by create_time";
											//System.out.println("sqloptions:"+sqloptions);
											List<Object[]> listopt = AccessDataBaseUtil.query(sqloptions.toString(), null);
											//最新更新日期与当前时间对比0----1-----2------最新更新-----当前
											for (int j = listopt.size()-1; j >=0 ; j--) {// 行
													int option = 0;
												if(listopt.get(j)[0].equals("五元") || listopt.get(j)[0].equals("0")) {
													option = 5;
												} else if(listopt.get(j)[0].equals("十元") || listopt.get(j)[0].equals("1")) {
													option = 10;
												}
												//System.out.println("option:"+option);
												if(j == listopt.size() - 1) {
													SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
													Date dd = new Date();
													//先需要判断独生子女的年龄是否已经满18岁
													//System.out.println("trpocfm_child_birthday:"+(Integer.parseInt(map.get("trpocfm_child_birthday").substring(0, 4))+18)+map.get("trpocfm_child_birthday").substring(4));
													if(FormatUtil.remainDateToString((Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4),ft.format(dd))<=0||ft.format(dd).equals((Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4)))
													{
														int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(), ft.format(dd));
														sum += month * option;
														sum = sum<0 ? 0 : sum;
													}
													else
													{
														//直接以出生日期之后的第18年为止
														int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(),(Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4));
														sum += month * option;
														sum = sum<0 ? 0 : sum;
													}

													if(listopt.size()==2)
													{
														if(listopt.get(0)[0].equals("五元") || listopt.get(0)[0].equals("0")) {
															option = 5;
														} else if(listopt.get(0)[0].equals("十元") || listopt.get(0)[0].equals("1")) {
															option = 10;
														}
														int mon = FormatUtil.remainDateToString(listopt.get(0)[1].toString(), listopt.get(j)[1].toString());
														sum += mon * option;
														sum = sum<0 ? 0 : sum;
														//System.out.println("sum:"+sum+"="+mon+"*"+option);
													}
												}
												if(listopt.size()!=2)
												{
													if(j >= 1) {
														int month = FormatUtil.remainDateToString(listopt.get(j - 1)[1].toString(), listopt.get(j)[1].toString());
														sum += month * option;
														//System.out.println("sum:"+sum+"="+month+"*"+option);
													}
												}
											}
											onlytotalMon += sum;
											//System.out.println("sum:"+sum+" totals: "+onlytotalMon);
										}
										else{onlytotalMon += Integer.parseInt(ids[1].toString());}

									}
									
								}
							}
							
							if(m>0)
							{
								sbs.append("{ \"name1\": \""+name+"\", \"orgid\": \""+subOrg[m]+"\",\"time1\": \""+time1+"\",\"time2\": \""+time2+"\",\"name2\": \""+onlytotal+"\", \"name3\": \""+onlytotalMon+"\", \"name4\": \""+exceedstotal+"\", \"name5\": \""+exceedstotalMon+"\", \"name6\": \""+retiretotals+"\", \"name7\": \""+retiretotalMons+"\", \"name8\": \""+retiretotal+"\", \"name9\": \""+retiretotalMon+"\"},");
							}
							else
							{
								sb.append("{\"Total\":\"1\",\"Rows\":[");
								sb.append("{ \"name1\": \""+name+"\", \"orgid\": \""+subOrg[m]+"\",\"time1\": \""+time1+"\",\"time2\": \""+time2+"\",\"name2\": \""+onlytotal+"\", \"name3\": \""+onlytotalMon+"\", \"name4\": \""+exceedstotal+"\", \"name5\": \""+exceedstotalMon+"\", \"name6\": \""+retiretotals+"\", \"name7\": \""+retiretotalMons+"\", \"name8\": \""+retiretotal+"\", \"name9\": \""+retiretotalMon+"\",");
							}
						}
					}
					len = (len>0) ? len-1 : len;
					if(!sbs.equals("")&&sbs.length()>0)
					{
						String sb1 = "{\"Total\":\""+len+"\",\"Rows\":[";
						String sb2 = sbs.substring(0, sbs.length()-1);
						sb_1.append(sb1);
						sb_1.append(sb2);
						sb_1.append("]}");
						//System.out.println(sb_1.toString());
						//汇总
						sb.append("\"children\": " + sb_1.toString() + "}");
						sb.append("]}");
					}
					else
					{
						//子机构children：
						String sb1 = "{\"Total\":\""+len+"\",\"Rows\":[";
						sbs.append("{ \"name1\": \"0\", \"name2\": \"0\", \"name3\": \"0\", \"name4\": \"0\", \"name5\": \"0\", \"name6\": \"0\", \"name7\": \"0\"},");
			
						String sb2 = sbs.substring(0, sbs.length()-1);
						sb_1.append(sb1);
						sb_1.append(sb2);
						sb_1.append("]}");
						
						if(sb.length()<1)
						{
							sb.append("{\"Total\":\"0\",\"Rows\":[");
							sb.append("{ \"name1\": \"一汽集团\", \"name2\": \"0\", \"name3\": \"0\", \"name4\": \"0\", \"name5\": \"0\", \"name6\": \"0\", \"name7\": \"0\",");
						}
						//汇总
						sb.append("\"children\": " + sb_1.toString() + "}");
						sb.append("]}");
					}
					//System.out.println(sb.toString());
				}else{
					sb.append("{\"Total\":\"0\",\"Rows\":[");
					sb.append("{ \"name1\": \"一汽集团\", \"name2\": \"0\", \"name3\": \"0\", \"name4\": \"0\", \"name5\": \"0\", \"name6\": \"0\", \"name7\": \"0\"},");
					sb.append("]}");
					}	  
			}else{
				sb.append("{\"Total\":\"0\",\"Rows\":[");
				sb.append("{ \"name1\": \"一汽集团\", \"name2\": \"0\", \"name3\": \"0\", \"name4\": \"0\", \"name5\": \"0\", \"name6\": \"0\", \"name7\": \"0\"},");
				sb.append("]}");
			}
		return sb.toString();

	}

	@Override
	public Object insert(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		return null;
	}

	/**
	 * 描述：expData 人员导出
	 * 
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn
	 * @author wangli
	 */
	public Object download(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/jcqkgl_tongji.xls";
		String file = url + "/file/jcqkgl_tongji.xls";
		String rfile = "jsp/app/xls/file/jcqkgl_tongji.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);
		List<Object[]> listtotal = new ArrayList<Object[]>();
		String[] subOrg;
		String strOrg="";
		String orgIds = jsonObject.getString("orgIds");//机构ID串
		if(StringUtils.isNotBlank(orgIds)){
			subOrg = user.getCurSubOrgIds(orgIds).split(",");
		}else{
			subOrg = user.getCurSubOrgId().split(",");
		}
		strOrg = user.getCurSubOrgIds(orgIds);//获取当前某一机构及以下的总数
		if(subOrg.length>0)
		{
			int len=0;
			for(int m=0;m<subOrg.length;m++)
			{
				//查询当前组织机构的名称
				//System.out.println("组织机构："+subOrg[m]);
				String empSql = "SELECT CAPTION FROM t_department WHERE DEPARTMENT_ID ="+subOrg[m]+"";
				List<Object> lemp = AccessDataBaseUtil.query4List(empSql,null);
				if(lemp.size()>0)
				{
					//查出独生子女费[0-18岁]
					String shengyu="SELECT trpocfm_id as id,reserve2 as org_id,trpocfm_child_birthday as types from trp_only_child_fee_management where 1=1 ";

					//超生缴纳社会抚养费[已经登记的家庭]
					String nobirth ="SELECT trpecp_id as id,trpecp_penalty as org_id,'exceeds' as types from trp_exceeds_children_penalty where 1=1";
					
					//退休独生子女父母奖励费[要求获取独生子女证的夫妻]
					String credent="SELECT r.trpocrr_id as id,e.tbe_id as org_id,'record' as types from tbe_only_child_record o LEFT JOIN tb_family_base f on o.tbfi_id=f.tbfi_id LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id LEFT JOIN trp_only_child_retire_reward r on e.tbe_id=r.tbe_id where o.reserve2<>'1' and e.tbe_employment_forms <> '31' and e.tbe_status not in('102-4','102-3') ";
					
					len++;
					String name = lemp.get(0).toString();
					//System.out.println("名称："+name);
					if(m>0)
					{
						shengyu += " and org_id in (" + subOrg[m] + ") ";
						nobirth += " and org_id in (" + subOrg[m] + ") ";
						credent += " and e.org_id in (" + subOrg[m] + ") ";
					}
					else
					{
						shengyu += " and org_id in (" + strOrg + ") ";
						nobirth += " and org_id in (" + strOrg + ") ";
						credent += " and e.org_id in (" + strOrg + ") ";
					}
					String time1 = jsonObject.getString("time1");// 开始时间
					String time2 = jsonObject.getString("time2");// 结束时间
					StringBuffer buff = new StringBuffer();
					if(StringUtils.isNotBlank(time1)) {
						//领证时间
						shengyu += "and trpocfm_ocr_time >= '"+time1+"'";
						//缴款日期
						nobirth += "and trpecp_payment_time >= '"+time1+"'";
					}
					if(StringUtils.isNotBlank(time2)) {
						//领证时间
						shengyu += "and trpocfm_ocr_time <= '"+time2+" 23:59:59'";
						//缴款日期
						nobirth += "and trpecp_payment_time <= '"+time2+" 23:59:59'";
					}
					
					if(StringUtils.isBlank(time1)&&StringUtils.isBlank(time2))
					{
						buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 60 YEAR)");
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 50 YEAR)");
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB(curdate(),INTERVAL 55 YEAR))");
						credent += buff.toString();
					}
					if(StringUtils.isNotBlank(time1)&&StringUtils.isNotBlank(time2))
					{
						buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
						buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday >= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR)").append(" and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
						credent += buff.toString();
					}
					else{
						if(StringUtils.isNotBlank(time1)) {
							//退休时间
							buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 60 YEAR)");//男职工
							buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 50 YEAR)");//女职工
							buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time1).append("',INTERVAL 55 YEAR))");//女干部
							credent += buff.toString();
							credent += buff.toString();
						}
						if(StringUtils.isNotBlank(time2)) {
							//退休时间
							buff.append(" and e.tbe_id in(SELECT tbe_id from tb_employee where tbe_sex='男' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 60 YEAR)");//男职工
							buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='2' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 50 YEAR)");//女职工
							buff.append(" UNION SELECT tbe_id from tb_employee where tbe_sex='女' and tbe_position='1' and tbe_birthday <= DATE_SUB('").append(time2).append("',INTERVAL 55 YEAR))");//女干部
							credent += buff.toString();
							credent += buff.toString();
						}
					}
//					System.out.println("querySql:"+shengyu);
//					System.out.println("querySql:"+nobirth);
//					System.out.println("querySql:"+credent);
					String querySql = shengyu +" UNION "+nobirth+" UNION "+credent;
					//String[] fields = {"id", "org_id","types"};
//					System.out.println("querySql:"+querySql);
					List<Object[]> list = AccessDataBaseUtil.query(querySql,null);
					
					int onlytotal=0;//总人数
					int onlytotalMon=0;//独生子女费总金额
					int exceedstotal=0;//超生总人数
					float exceedstotalMon=0;//超生总金额
					int retiretotals=0;//已发放退休总人数
					float retiretotalMons=0;//已发放退休总金额
					int retiretotal=0;//退休总人数
					float retiretotalMon=0;//退休总金额
					if (list.size() > 0) {
						for (int i = 0; i < list.size(); i++) {// 行
							int sum=0;
							Object[] ids=list.get(i);
							if(ids[2].toString().equals("exceeds"))//超生
							{
								exceedstotal++;
								if(!ids[1].toString().equals("") && ids[1].toString() != null) {
									exceedstotalMon += Float.parseFloat(ids[1].toString());
									}
							}
							else if(ids[2].toString().equals("record"))//退休
							{
								//已发放
								if (!ids[0].toString().equals("")) {
									retiretotals += 1;
									retiretotalMons +=user.getGlobalMap().get("Retirement").equals("") ? 2000 : Float.parseFloat(user.getGlobalMap().get("Retirement"));
								}
								retiretotal++;
								//男职工2000元，女职工2000元，女干部2000元
								//从参数中取得:user.getGlobalMap().get("reward")
								retiretotalMon +=user.getGlobalMap().get("Retirement").equals("") ? 2000 : Float.parseFloat(user.getGlobalMap().get("Retirement"));
							}
							else
							{
								onlytotal++;
								if(ids[1].toString().equals(""))//查看证件是否注销
								{
									String sqloptions="Select trpocfm_fee_option,create_time from trp_only_child_fee_options where trpocfm_id = '"+ids[0].toString()+"' order by create_time";
//									System.out.println("sqloptions:"+sqloptions);
									List<Object[]> listopt = AccessDataBaseUtil.query(sqloptions.toString(), null);
									//最新更新日期与当前时间对比0----1-----2------最新更新-----当前
									for (int j = listopt.size()-1; j >=0 ; j--) {// 行
											int option = 0;
										if(listopt.get(j)[0].equals("五元") || listopt.get(j)[0].equals("0")) {
											option = 5;
										} else if(listopt.get(j)[0].equals("十元") || listopt.get(j)[0].equals("1")) {
											option = 10;
										}
										//System.out.println("option:"+option);
										if(j == listopt.size() - 1) {
											SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
											Date dd = new Date();
											//先需要判断独生子女的年龄是否已经满18岁
											//System.out.println("trpocfm_child_birthday:"+(Integer.parseInt(map.get("trpocfm_child_birthday").substring(0, 4))+18)+map.get("trpocfm_child_birthday").substring(4));
											if(FormatUtil.remainDateToString((Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4),ft.format(dd))<=0||ft.format(dd).equals((Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4)))
											{
												int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(), ft.format(dd));
												sum += month * option;
												sum = sum<0 ? 0 : sum;
											}
											else
											{
												//直接以出生日期之后的第18年为止
												int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(),(Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4));
												sum += month * option;
												sum = sum<0 ? 0 : sum;
											}

											if(listopt.size()==2)
											{
												if(listopt.get(0)[0].equals("五元") || listopt.get(0)[0].equals("0")) {
													option = 5;
												} else if(listopt.get(0)[0].equals("十元") || listopt.get(0)[0].equals("1")) {
													option = 10;
												}
												int mon = FormatUtil.remainDateToString(listopt.get(0)[1].toString(), listopt.get(j)[1].toString());
												sum += mon * option;
												sum = sum<0 ? 0 : sum;
												//System.out.println("sum:"+sum+"="+mon+"*"+option);
											}
										}
										if(listopt.size()!=2)
										{
											if(j >= 1) {
												int month = FormatUtil.remainDateToString(listopt.get(j - 1)[1].toString(), listopt.get(j)[1].toString());
												sum += month * option;
												//System.out.println("sum:"+sum+"="+month+"*"+option);
											}
										}
									}
									onlytotalMon += sum;
									//System.out.println("sum:"+sum+" totals: "+onlytotalMon);
								}
								else{onlytotalMon += Integer.parseInt(ids[1].toString());}

							}
							
						}
					}
					
					if(m>0)
					{
						String[] arr = {name,String.valueOf(onlytotal),String.valueOf(onlytotalMon),String.valueOf(exceedstotal),String.valueOf(exceedstotalMon),String.valueOf(retiretotals),String.valueOf(retiretotalMons),String.valueOf(retiretotal),String.valueOf(retiretotalMon)};
						listtotal.add(arr);
					}
					else
					{
						String[] arr = {name,String.valueOf(onlytotal),String.valueOf(onlytotalMon),String.valueOf(exceedstotal),String.valueOf(exceedstotalMon),String.valueOf(retiretotals),String.valueOf(retiretotalMons),String.valueOf(retiretotal),String.valueOf(retiretotalMon)};
						listtotal.add(arr);
					}
				}
			}
		}else{
			String[] arr = {"一汽集团","0","0","0","0","0","0","0","0"};
			listtotal.add(arr);
		}

		String date = FormatUtil.getCurentDate();

//		List<Object[]> listtotals = new ArrayList<Object[]>();
//		String[] arr = {"铸造厂","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21"};
//		listtotals.add(arr);
		if(listtotal.size() > 0){
			POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(fto));
			HSSFWorkbook book = new HSSFWorkbook(filein);
			HSSFSheet sheet = book.getSheet("奖惩情况统计表");
			int startRow = 4;
			if(null != listtotal && listtotal.size() > 0){
				HSSFRow row = null;
				row = sheet.createRow(1);
				row.setHeight((short) 500);
				HSSFCell cell1_0 = row.createCell(0);
				HSSFCellStyle cstyle1 = book.createCellStyle();
				cstyle1.setAlignment(CellStyle.ALIGN_RIGHT);
				cstyle1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				cell1_0.setCellStyle(cstyle1);
				cell1_0.setCellValue(date);
				
				for (int i = 0; i < listtotal.size(); i++) {
					row = sheet.createRow(i + startRow);
					row.setHeight((short) 400);
					
					//序号
					HSSFCell cell = row.createCell(0);
					cell.setCellValue((i+1) + "");
					
					Object[] metaData = listtotal.get(i);
					for(int j = 0; j < metaData.length; j++) {
						cell = row.createCell(j + 1);
						// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
						String textValue = metaData[j].toString();
						cell.setCellValue(new HSSFRichTextString(textValue));
					}
				}
			
			}
			
			FileOutputStream fileOut = new FileOutputStream(file);
			book.write(fileOut);
			fileOut.close();
			
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"报表中没有记录\"}";
		}
		
		return "\"" + rfile + "\"";
	}
	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
}